//
//  YbrClockDao.m
//  wifibox
//
//  Created by os X 10.9 on 14-12-9.
//  Copyright (c) 2014年 yinbaner. All rights reserved.
//  定时



#import "YbrClockDao.h"
#import "YbrClockItem.h"
#import "YbrClockTimerItem.h"
#import "YbrMusicItem.h"
#import "YbrTimerUtil.h"
#import "YbrBeginAndEndTime.h"
#import "YbrNoTimeList.h"

#import "YbrDlnaClassItem.h"
#import "YbrDlnaMusic.h"
#import "YbrDlnaMyTime.h"

@implementation YbrClockDao

/**
 * clock 定时表  _id uuid  name
 * clock_timer 定时和时间的中间表  (_id ,timerid ,weekday ,beginTime ,endTime ,isopen)
 * clock_music 定时和文件的中间表  (_id ,clocktime_id ,type ,name ,url ,duration ,singername ,albumname ,file,img)
 *
 */
-(YbrNoTimeList *) createTimer:(int) timerid {
    __block YbrNoTimeList *noTimeList;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        
        NSLog(@"timeID    %i" ,timerid);
        
        rs                      =   [db executeQueryWithFormat:@"select _id, weekday ,beginTime, endTime from clock_timer where timerid=%i" ,timerid];
        noTimeList              =   [[YbrNoTimeList alloc] init];
        
        NSString *lastListID    =   [NSString stringWithFormat:@"%li" ,(long)([[NSDate date] timeIntervalSince1970] * 1000)];
        noTimeList.id           =   lastListID;
        noTimeList.FileType     =   @"1";
        
        NSMutableArray *dlnaClassItemArr    =   [NSMutableArray array];;
        YbrDlnaClassItem *dlnaClassItem     =   nil;
        
        NSMutableArray *dlnamusics  =   nil;
        YbrDlnaMusic *dlnaMusic     =   nil;
        
        NSMutableArray *dlnaTimes   =   nil;
        YbrDlnaMyTime *dlnaTime     =   nil;
        
        while (rs.next) {
            int _id         =   [rs intForColumn:@"_id"];
            int beginTime   =   [rs intForColumn:@"beginTime"];
            int endTime     =   [rs intForColumn:@"endTime"];
            int week        =   [rs intForColumn:@"weekday"];
            
            //========
            
            //NSLog(@"定时id        %d", _id);
            
            //========
            
            FMResultSet *rs2    =   [db executeQueryWithFormat:@"select * from clock_music where clocktime_id=%i" ,_id];
            if(rs2.next) {
                dlnaTimes       =   [NSMutableArray array];
                
                dlnaTime        =   [YbrDlnaMyTime InitByValue:[YbrTimerUtil makeClockChooseTimeStr:beginTime] endTime:[YbrTimerUtil makeClockChooseTimeStr:endTime] weekDay:[NSString stringWithFormat:@"%i" ,week + 1]];
                [dlnaTimes addObject:dlnaTime];
                
                dlnaClassItem           =   [[YbrDlnaClassItem alloc] init];
                dlnaClassItem.module    =   PlayModeOne;
                
                
                dlnamusics  = [NSMutableArray array];
                
                int myPost  =   0;
                
                do{
                    myPost ++;
                    
                    NSString *url       =   [rs2 stringForColumn:@"url"];
                    dlnaMusic           =   [[YbrDlnaMusic alloc] init];
                    dlnaMusic.position  =   myPost;
                    
                    dlnaMusic.id         = [rs2 stringForColumn:@"musicid"];
                    dlnaMusic.name       = [rs2 stringForColumn:@"name"];
                    dlnaMusic.singerName = [rs2 stringForColumn:@"singername"];
                    dlnaMusic.albumName  = [rs2 stringForColumn:@"albumname"];
                    dlnaMusic.img        = [rs2 stringForColumn:@"img"];
                    
                    
                    //NSLog(@"------------查看图片链接    %@",dlnaMusic.img);
                    
                    
                    //判断资源是云课堂,还是喜马拉雅
                    if ([url rangeOfString:@"http://"].location == NSNotFound){
//                        dlnaMusic.url = [NSString stringWithFormat:@"%@%@" ,MP3Url ,url];
                    }
                    else{
                        dlnaMusic.url = url;
                    }
                    
                    
                    if([dlnaMusic.singerName isEqualToString:YbrDefualSingerName]) {    // 表示是网络歌曲
                        dlnaMusic.saveurl = [NSString stringWithFormat:@"%@%@%@" ,FTPDownTemp ,dlnaMusic.name ,@".mp3"];
                    }else {
//                        dlnaMusic.saveurl = [NSString stringWithFormat:@"%@%@%@" ,FTPDownTemp , [YbrMD5 md5HexDigest:[NSString stringWithFormat:@"%@%@" ,dlnaMusic.albumName , dlnaMusic.name]] ,@".mp3"];
                    }
                    
                    dlnaMusic.duration = [YbrTimerUtil makeClockChooseTimeStr:[rs2 intForColumn:@"duration"]];
                    [dlnamusics addObject:dlnaMusic];
                }while (rs2.next);
                
                dlnaClassItem.timers = [dlnaTimes copy];
                dlnaClassItem.musics = [dlnamusics copy];
                //这个时间戳有问题
                //dlnaClassItem.id = [NSString stringWithFormat:@"%li" ,(long)[[NSDate date] timeIntervalSince1970] * 1000]; // 辛乐
                dlnaClassItem.id = [self createUniqueNumber];
                [dlnaClassItemArr addObject:dlnaClassItem];
            }
            noTimeList.classItems = dlnaClassItemArr;
        }
        [rs close];
    }];
    return noTimeList;
}


// 复制定时
-(BOOL)copyClockById:(int)timerid newClockName:(NSString *)newClockName
{
    __block BOOL isOk = YES;
    // 2. 查询定时的时间段 + 时间段 + 音乐
    [[YbrSQLHelper getDataBase] inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL isRunOK;
        isRunOK         = [db executeUpdate:@"insert into clock (name) VALUES (?)", newClockName];
        int newTimerId  = (int)[db lastInsertRowId];
        
        if(!isRunOK)
        {
            isOk = NO;
            *rollback = YES;
            return;
        }
        
        // 所有时间段
        FMResultSet *rs;
        for (int i = 0; i < 7; i ++)
        {
            int weekday = i;
            // 1.查询数据
            rs = [db executeQueryWithFormat:@"select timer._id _id, timer.timerid timerid, timer.beginTime beginTime,timer.endTime endTime, timer.isopen isopen,SUM(music.duration) timeall ,weekday from clock_timer timer left join clock_music music on timer._id = music.clocktime_id where timer.timerid=%i and timer.weekday=%i GROUP BY timer.beginTime ORDER BY timer.beginTime" ,timerid ,weekday];
            while (rs.next)
            {
                int beginTime       = [rs intForColumn:@"beginTime"];
                int endTime         = [rs intForColumn:@"endTime"];
                int clockTimerId    = [rs intForColumn:@"_id"];
//                NSLog(@"---复制时间---时间段----------%d---%@--------%@-----%d",clockTimerId,[YbrTimerUtil makeTimeStr:beginTime],[YbrTimerUtil makeTimeStr:endTime],endTime);
                BOOL isRunOK        = [db executeUpdateWithFormat:@"insert into clock_timer (timerid ,weekday ,beginTime ,endTime) values (%i ,%i ,%i ,%i)", newTimerId ,weekday ,beginTime ,endTime];
                int newClockTimerId = (int)[db lastInsertRowId];
                
                if (!isRunOK)
                {
                    isOk = NO;
                    break;
                }
//                NSLog(@"---复制时间---新的时间段----------%d--",newClockTimerId);
                FMResultSet *rs2;
                rs2 = [db executeQueryWithFormat:@"select * from clock_music where clocktime_id = %i" ,clockTimerId];
                
                while (rs2.next)
                {
                    YbrMusicItem *musicItem     = [[YbrMusicItem alloc] init];
                    musicItem.musicName         = [rs2 stringForColumn:@"name"];
                    musicItem.duration          = [rs2 intForColumn:@"duration"];
                    musicItem.timeStr           = [YbrTimerUtil makeTimeStr:musicItem.duration];
                    musicItem.downloadurl       = [rs2 stringForColumn:@"url"];
                    musicItem.type              = [rs2 intForColumn:@"type"];
                    musicItem.musicId           = [rs2 intForColumn:@"musicid"];
                    musicItem.singername        = [rs2 stringForColumn:@"singername"];
                    musicItem.albumname         = [rs2 stringForColumn:@"albumname"];
                    musicItem.file              = [rs2 stringForColumn:@"file"];
                    musicItem.img               = [rs2 stringForColumn:@"img"];
//                    NSLog(@"----复制定时---------%d-----%@-----%@----%@---",musicItem.musicId,musicItem.musicName,musicItem.singername,musicItem.img);
                    isRunOK = [db executeUpdateWithFormat:@"insert into clock_music (name ,clocktime_id ,url ,duration ,type ,musicid ,singername ,albumname ,file ,img) values (%@ ,%i ,%@ ,%i ,%i ,%i ,%@ ,%@ ,%@ ,%@) " ,
                        musicItem.musicName ,
                        newClockTimerId ,
                        musicItem.downloadurl ,
                        musicItem.duration,
                        musicItem.type ,
                        musicItem.musicId ,
                        musicItem.singername ,
                        musicItem.albumname ,
                        musicItem.file ,
                        musicItem.img];
                    
//                    int newMusicId = (int)[db lastInsertRowId];
//                    NSLog(@"----复制定时-----新音乐ID----%d-----%d",newMusicId,isRunOK);
                    
                    if(!isRunOK)
                    {
                        isOk = NO;
                        *rollback = YES;
                        break;
                    }
                }
                [rs2 close];
            }
            [rs close];
        }
    }];
    return isOk;
}


// 创建一个随机数,随机产生唯一数
-(NSString *) createUniqueNumber
{
    
    NSString *strRandom = @"";
    for(int i=0; i<16; i++)
    {
        strRandom = [strRandom stringByAppendingFormat:@"%i",(arc4random() % 9)];
    }
    NSLog(@"时间id  %@",strRandom);
    return strRandom;
}


// 这里的arr存放是YbrClockItem对象
-(NSArray *) getAllClock
{
    __block NSArray *arr = [[NSArray alloc] init];
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select * from clock order by _id desc"];
        
        NSMutableArray *tempArr = [NSMutableArray array];
        while (rs.next)
        {
            YbrClockItem *item = [[YbrClockItem alloc] init];
            item._id = [rs intForColumn:@"_id"];
            item.uuid = [rs stringForColumn:@"uuid"];
            item.name = [rs stringForColumn:@"name"];
            [tempArr addObject:item];
        }
        [rs close];
        arr = [tempArr copy];
    }];
    return arr;
}


-(BOOL) addClock:(NSString *)name
{
    __block BOOL isOk = NO;
    
    [[YbrSQLHelper getDBHandler] inDatabase:^(FMDatabase *db) {
        isOk = [db executeUpdate:@"insert into clock (name) VALUES (?)", name];
    }];
    return isOk;
}


-(BOOL) addDownClock:(NSString *)name uuid:(NSString *)uuid
{
    __block BOOL isOk = NO;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        isOk = [db executeUpdate:@"insert into clock (name,uuid) VALUES (?,?)", name,uuid];
    }];
    return isOk;
}


// 获取定时的_id
-(int) getTimerIdByuuid:(NSString *) uuid
{
    __block int timerId;
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select _id from clock where uuid=%@" ,uuid];
        
        while (rs.next) {
            timerId = [rs intForColumn:@"_id"];
            break;
        }
        [rs close];
    }];
    return timerId;
}


// 判断定时是否有uuid
-(int) isHaveUuid:(NSString *) uuid
{
    __block int count;
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select count(uuid) from clock where uuid=%@" ,uuid];
        
        while (rs.next) {
            count = [rs intForColumn:@"count(uuid)"];
            break;
        }
        [rs close];
    }];
    return count;
}

// 判断定时列表是否有该列表
-(int) isHaveClockName:(NSString *) name
{
    __block int count;
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select count(uuid) from clock where name=%@" ,name];
        
        while (rs.next) {
            count = [rs intForColumn:@"count(uuid)"];
            break;
        }
        [rs close];
    }];
    return count;
}


// 获取定时的名称
-(NSString *) getTimerNameById:(int) timerid
{
    __block NSString *timerName;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select name from clock where _id=%i" ,timerid];
        
        while (rs.next)
        {
            timerName = [rs stringForColumn:@"name"];
            break;
        }
        [rs close];
    }];
    return timerName;
}


// 根据星期获取时间列表
// 这里存放的是YbrClockTimerItem对象
-(NSArray *) getClockByWeekday:(int) timerid weekday:(int) weekday
{
    __block NSArray *arr;
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select timer._id _id, timer.timerid timerid, timer.beginTime beginTime,timer.endTime endTime, timer.isopen isopen,SUM(music.duration) timeall ,weekday from clock_timer timer left join clock_music music on timer._id = music.clocktime_id where timer.timerid=%i and timer.weekday=%i GROUP BY timer.beginTime ORDER BY timer.beginTime" ,timerid ,weekday];
        
        NSMutableArray *tempArr = [NSMutableArray array];
        while (rs.next) {
            YbrClockTimerItem *clockTimerItem   = [[YbrClockTimerItem alloc] init];
            clockTimerItem._id                  = [rs intForColumn:@"_id"];
            clockTimerItem.timerid              = [rs intForColumn:@"timerid"];
            clockTimerItem.beginTime            = [rs intForColumn:@"beginTime"];
            clockTimerItem.endTime              = [rs intForColumn:@"endTime"];
            clockTimerItem.isopen               = [rs intForColumn:@"isopen"];
            clockTimerItem.timeall              = [rs intForColumn:@"timeall"];
            clockTimerItem.weekday              = [rs intForColumn:@"weekday"];
            [tempArr addObject:clockTimerItem];
        }
        [rs close];
        arr = [tempArr copy];
    }];
    return arr;
}


// 根据时间ID + 星期获取开始时间和结束时间
// 获得现在这个日期当中的所有开始和结束的日期(Arr[0] 表示开始时间  Arr[1]表示结束时间)
-(NSArray *) getClockBeginAndEndByWeek:(int) weekday timerid:(int) timerid
{
    __block NSArray *arr;
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select beginTime ,endTime from clock_timer where weekday=%i and timerid=%i order by beginTime" ,weekday ,timerid];
        NSMutableArray *resultArr = [NSMutableArray array];
        
        NSMutableArray *beginArr = [NSMutableArray array];
        NSMutableArray *endArr = [NSMutableArray array];
        while (rs.next)
        {
            [beginArr addObject:[NSNumber numberWithInt:[rs intForColumn:@"beginTime"]]];
            [endArr addObject:[NSNumber numberWithInt:[rs intForColumn:@"endTime"]]];
        }
        [rs close];
        
        [resultArr addObject:beginArr];
        [resultArr addObject:endArr];
        
        arr = [resultArr copy];
    }];
    return arr;
}


//  根据时间段获取音乐列表
// 获得这个时间段的所有时间的总和
-(int) getAllMusicTime:(int )clocktimeId {
    __block int alltime = 0;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        
        FMResultSet *rs;
        rs = [db executeQueryWithFormat:@"select sum(duration) alltime from clock_music where clocktime_id=%i" ,clocktimeId];
        
        while (rs.next) {
            alltime = [rs intForColumn:@"alltime"];
            break;
        }
        [rs close];
    }];
    return alltime;
}


// 添加Array music到时间数据库中
-(BOOL) addMusicToWeekDay:(NSArray *) musicChoose  weekDay:(int)weekDay timerid:(int)timerid begintime:(int)begintime endtime:(int)endtime
{
    __block BOOL isOK = YES;

    [[YbrSQLHelper getDataBase] inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL isRunOK;
        isRunOK = [db executeUpdateWithFormat:@"insert into clock_timer (timerid ,weekday ,beginTime ,endTime) values (%i ,%i ,%i ,%i)", timerid ,weekDay ,begintime ,endtime];
        
        if(!isRunOK)
        {
            isOK = NO;
            *rollback = YES;
        }
        
        int clockTimerID = 0;
        
        FMResultSet *rs;
        rs = [db executeQueryWithFormat:@"select _id from clock_timer where timerid=%i and weekday=%i and beginTime=%i and endTime=%i" ,timerid ,weekDay ,begintime ,endtime];
        
        while (rs.next)
        {
            clockTimerID = [rs intForColumn:@"_id"];
            break;
        }
        [rs close];
        
        for (int i = 0; i < [musicChoose count]; i ++)
        {
            YbrMusicItem *musicItem = (YbrMusicItem *) musicChoose[i];
            isRunOK = [db executeUpdateWithFormat:@"insert into clock_music (name ,clocktime_id ,url ,duration ,type ,musicid ,singername ,albumname ,file ,img) values (%@ ,%i ,%@ ,%i ,%i ,%i ,%@ ,%@ ,%@ ,%@) " ,
                       musicItem.musicName ,
                       clockTimerID ,
                       musicItem.downloadurl ,
                       musicItem.duration,
                       musicItem.type ,
                       musicItem.musicId ,
                       musicItem.singername ,
                       musicItem.albumname ,
                       musicItem.file ,
                       musicItem.img];
            
            if(!isRunOK)
            {
                isOK = NO;
                *rollback = YES;
                break;
            }
        }
        
    }];
    return isOK;
}


-(int) getClockTimeId:(int) timerId weekday:(int) weekday beginTime:(int)beginTime endTime:(int) endTime
{
    __block int clockTimerID = -1;
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        rs = [db executeQueryWithFormat:@"select _id from clock_timer where timerid=%i and weekday=%i and beginTime=%i and endTime=%i" ,timerId ,weekday ,beginTime ,endTime];
        
        while (rs.next)
        {
            clockTimerID = [rs intForColumn:@"_id"];
            break;
        }
        [rs close];

    }];
    return clockTimerID;
}


// arr 是YbrMusicItem
-(NSArray *) getMusicListByWeekAndBegin:(int) _id
{
    __block NSArray *arr;
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select name ,url ,duration ,type ,musicid ,singername ,albumname ,file ,img from clock_music where clocktime_id=%i" ,_id];
        
        NSMutableArray *tempArr = [NSMutableArray array];
        while (rs.next)
        {
            YbrMusicItem *musicItem     = [[YbrMusicItem alloc] init];
            musicItem.musicName         = [rs stringForColumn:@"name"];
            musicItem.duration          = [rs intForColumn:@"duration"];
            musicItem.timeStr           = [YbrTimerUtil makeTimeStr:musicItem.duration];
            musicItem.downloadurl       = [rs stringForColumn:@"url"];
            musicItem.type              = [rs intForColumn:@"type"];
            musicItem.musicId           = [rs intForColumn:@"musicid"];
            musicItem.singername        = [rs stringForColumn:@"singername"];
            musicItem.albumname         = [rs stringForColumn:@"albumname"];
            musicItem.file              = [rs stringForColumn:@"file"];
            musicItem.img               = [rs stringForColumn:@"img"];
            [tempArr addObject:musicItem];
        }
        [rs close];
        arr = [tempArr copy];
    }];
    return arr;
}


-(BOOL) delOldTime:(int) clocktimeId {
    __block BOOL isOk = NO;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        isOk = [db executeUpdateWithFormat:@"delete from clock_timer where _id=%i", clocktimeId];
        isOk = [db executeUpdateWithFormat:@"delete from clock_music where clocktime_id=%i" ,clocktimeId];
    }];
    return isOk;
}


// 这个里面有YbrClockTimerItem YbrMusicItem对象和
//-(FMResultSet *) copyContent:(int) clocktimeId {
//    __block FMResultSet *rs;
//    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
//        
//        // 1.查询数据
//        rs = [db executeQueryWithFormat:@"select timer._id _id, timer.beginTime beginTime,timer.endTime endTime, music.musicid musicid, music.name name ,music.url url ,music.duration duration ,music.singername singername, music.albumname albumname, music.file file ,music.img img ,music.type type from clock_timer timer left join clock_music music on timer._id = music.clocktime_id  WHERE timer._id= %i" ,clocktimeId];
//        
//    }];
//    return rs;
//}


// 拷贝的是开始时间和结束时间(arr[0] beginTime arr[1]表示endTime)
-(NSArray *) copyTime:(int) clocktimeId
{
    __block NSArray *arr;
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select beginTime ,endTime from clock_timer where _id=%i" ,clocktimeId];
        
        NSMutableArray *tempArr = [NSMutableArray array];
        while (rs.next)
        {
            YbrBeginAndEndTime *beginAndEnd = [[YbrBeginAndEndTime alloc] init];
            beginAndEnd.beginTime = [rs intForColumn:@"beginTime"];
            beginAndEnd.endTime = [rs intForColumn:@"endTime"];
            
            [tempArr addObject:beginAndEnd];
        }
        [rs close];
        arr = [tempArr copy];
    }];
    return arr;
}


-(BOOL) deleTimerByTimerIdAndWeek:(int) timerid weekday:(int) weekday timerInt:(NSArray *) timerInt
{
    __block BOOL isOK = NO;
    
    [[YbrSQLHelper getDataBase] inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL isRunOK;
       
        for (int i = 0; i < [timerInt count]; i ++)
        {
            FMResultSet *rs;
            rs = [db executeQueryWithFormat:@"select _id from clock_timer where timerid=%i and weekday=%i and beginTime=%i",
                  timerid ,weekday ,[((NSArray *)timerInt[i])[0] intValue]];
            
            while (rs.next)
            {
                int clockTimeId = [rs intForColumn:@"_id"];
                
                isRunOK = [db executeUpdateWithFormat:@"delete from clock_timer where _id=%i" ,clockTimeId];
                if(!isRunOK)
                {
                    isOK = NO;
                    *rollback = YES;
                    break;
                }

                isRunOK = [db executeUpdateWithFormat:@"delete from clock_music where clocktime_id=%i", clockTimeId];
                if(!isRunOK)
                {
                    isOK = NO;
                    *rollback = YES;
                    break;
                }
            }
        }
    }];
    return isOK;
}


-(BOOL) addCopyContent:(int) copyClockTimeId timerid:(int) timerid weekday:(int) weekday
{
    __block BOOL isOK = NO;
    
    [[YbrSQLHelper getDataBase] inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL isRunOK;
        // 这里表示取出第一个
        FMResultSet * copyContent  = [db executeQueryWithFormat:@"select timer._id _id, timer.beginTime beginTime,timer.endTime endTime, music.musicid musicid, music.name name ,music.url url ,music.duration duration ,music.singername singername, music.albumname albumname, music.file file ,music.img img ,music.type type from clock_timer timer left join clock_music music on timer._id = music.clocktime_id  WHERE timer._id= %i" ,copyClockTimeId];
        
        if(!copyContent.next)
        {
            isOK = NO;
            return;
        }
        
        int beginTime = [copyContent intForColumn:@"beginTime"];
        int endTime = [copyContent intForColumn:@"endTime"];
        
        isRunOK = [db executeUpdateWithFormat:@"insert into clock_timer (timerid ,weekday ,beginTime ,endTime) values (%i ,%i ,%i ,%i)", timerid ,weekday ,beginTime ,endTime];
        
        FMResultSet *set = [db executeQueryWithFormat:@"select _id from clock_timer where timerid=%i and weekday=%i and beginTime=%i" ,timerid ,weekday ,beginTime];
        
        int _id = -1;
        while (set.next)
        {
            _id = [set intForColumn:@"_id"];
            break;
        }
        [set close];
        
        do{
            isRunOK = [db executeUpdateWithFormat:@"insert into clock_music (name ,clocktime_id ,url ,duration ,type ,musicid ,singername ,albumname ,file ,img) values (%@ ,%i ,%@ ,%i ,%i ,%i ,%@ ,%@ ,%@ ,%@) " ,
                       [copyContent stringForColumn:@"name"] ,
                       _id ,
                       [copyContent stringForColumn:@"url"],
                       [copyContent intForColumn:@"duration"],
                       [copyContent intForColumn:@"type"] ,
                       [copyContent intForColumn:@"musicid"],
                       [copyContent stringForColumn:@"singername"] ,
                       [copyContent stringForColumn:@"albumname"] ,
                       [copyContent stringForColumn:@"file"] ,
                       [copyContent stringForColumn:@"img"]];
        }while (copyContent.next);
    }];
    return isOK;
}


// 获得这个Item的音频的时间总长度
-(int) getWeekItemTimeLeng:(int) clocktimeId
{
    __block int timeLeng;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select sum(duration) timeall from clock_music where clocktime_id=%i" ,clocktimeId];
        
        while (rs.next) {
            timeLeng = [rs intForColumn:@"timeall"];
            break;
        }
        [rs close];
    }];
    return timeLeng;
}


// 获得这个Item的定时长度
-(int) getWeekItemAllTimeLeng:(int) _id
{
    __block int timeLeng;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select endTime-beginTime cha from clock_timer where _id=%i" ,_id];
        
        while (rs.next)
        {
            timeLeng = [rs intForColumn:@"timeall"];
            break;
        }
        [rs close];
    }];
    return timeLeng;
}

// 替换相应时间的内容(先删除后插入)
-(BOOL) replaceTimerContent:(int)copyClockTimeId clocktimeId:(int) clcoktimeId
{
    __block BOOL isOK = NO;
    
    [[YbrSQLHelper getDataBase] inTransaction:^(FMDatabase *db, BOOL *rollback) {
        [db executeUpdateWithFormat:@"delete from clock_music where clocktime_id=%i" ,clcoktimeId];
        
        BOOL isRunOK;
        FMResultSet * copyContent  = [db executeQueryWithFormat:@"select timer._id _id, timer.beginTime beginTime,timer.endTime endTime, music.musicid musicid, music.name name ,music.url url ,music.duration duration ,music.singername singername, music.albumname albumname, music.file file ,music.img img ,music.type type from clock_timer timer left join clock_music music on timer._id = music.clocktime_id  WHERE timer._id= %i" ,copyClockTimeId];
        
//        if(!copyContent.next)
//        {
//            isOK = NO;
//            return;
//        }
        
        while (copyContent.next)
        {
            isRunOK = [db executeUpdateWithFormat:@"insert into clock_music (name ,clocktime_id ,url ,duration ,type ,musicid ,singername ,albumname ,file ,img) values (%@ ,%i ,%@ ,%i ,%i ,%i ,%@ ,%@ ,%@ ,%@) " ,
                       [copyContent stringForColumn:@"name"] ,
                       clcoktimeId ,
                       [copyContent stringForColumn:@"url"],
                       [copyContent intForColumn:@"duration"],
                       [copyContent intForColumn:@"type"] ,
                       [copyContent intForColumn:@"musicid"],
                       [copyContent stringForColumn:@"singername"] ,
                       [copyContent stringForColumn:@"albumname"] ,
                       [copyContent stringForColumn:@"file"] ,
                       [copyContent stringForColumn:@"img"]];
            
            if(!isRunOK) {
                isOK = NO;
                *rollback = YES;
                return;
            }
        };
        isOK = YES;
    }];
    return isOK;
}


// 替换相应时间的内容(先删除后插入)
-(NSMutableArray *) getClockTimeMusics:(int) copyClockTimeId
{
    NSMutableArray * datasArray = [[NSMutableArray alloc] init];
    
    [[YbrSQLHelper getDataBase] inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet * copyContent  = [db executeQueryWithFormat:@"select name ,url ,duration ,type ,musicid ,singername ,albumname ,file ,img from clock_music where clocktime_id=%i" ,copyClockTimeId];
        
        while (copyContent.next)
        {
            NSMutableDictionary * dict = [[NSMutableDictionary alloc] init];
            [dict setValue:[copyContent stringForColumn:@"name"] forKey:@"name"];
            [datasArray addObject:dict];
        };
    }];
    return datasArray;
}


// 复制星期(checks表示已经选择的)
-(BOOL) copyWeekday:(int) timerid weekday:(int) weekday checks:(NSArray *) checks
{
    __block BOOL isOK = NO;
    
    [[YbrSQLHelper getDataBase] inTransaction:^(FMDatabase *db, BOOL *rollback) {
        BOOL isRunOK;
        
        FMResultSet *rs;
        // 先删除表中的信息
        for (int i = 0; i < [checks count]; i ++)
        {
            rs = [db executeQueryWithFormat:@"select _id from clock_timer where timerid=%i and weekday=%i" ,timerid ,[checks[i] intValue]];
            
            while (rs.next)
            {
                int _id = [rs intForColumn:@"_id"];
                [db executeUpdateWithFormat:@"delete from clock_timer where _id=%i" ,_id];
                [db executeUpdateWithFormat:@"delete from clock_music where clocktime_id=%i" ,_id];
            }
        }
        
        rs = [db executeQueryWithFormat:@"select _id ,beginTime ,endTime ,isopen from clock_timer where timerid=%i and weekday=%i" ,timerid ,weekday];
        while (rs.next)
        {
            for (int i = 0; i < [checks count]; i ++)
            {
                [db executeUpdateWithFormat:@"insert into clock_timer (timerid ,weekday ,beginTime ,endTime ,isopen) values (%i ,%i ,%i ,%i ,%i)" ,timerid ,[checks[i] intValue] ,[rs intForColumn:@"beginTime"] ,[rs intForColumn:@"endTime"] , [rs intForColumn:@"isopen"]];
                
                FMResultSet *_idRs;
                _idRs = [db executeQueryWithFormat:@"select _id from clock_timer where timerid=%i and weekday=%i and beginTime=%i" ,timerid ,[checks[i] intValue] ,[rs intForColumn:@"beginTime"]];
                int _id = 0;
                while (_idRs.next)
                {
                    _id = [_idRs intForColumn:@"_id"];
                    break;
                }
                [_idRs close];
                
                FMResultSet *timerRs;
                timerRs = [db executeQueryWithFormat:@"select * from clock_music where clocktime_id=%i" ,[rs intForColumn:@"_id"]];
                
                while (timerRs.next)
                {
                    isRunOK = [db executeUpdateWithFormat:@"insert into clock_music (name ,clocktime_id ,url ,duration ,type ,musicid ,singername ,albumname ,file ,img) values (%@ ,%i ,%@ ,%i ,%i ,%i ,%@ ,%@ ,%@ ,%@) " ,
                               [timerRs stringForColumn:@"name"] ,
                                _id,
                               [timerRs stringForColumn:@"url"],
                               [timerRs intForColumn:@"duration"],
                               [timerRs intForColumn:@"type"] ,
                               [timerRs intForColumn:@"musicid"],
                               [timerRs stringForColumn:@"singername"] ,
                               [timerRs stringForColumn:@"albumname"] ,
                               [timerRs stringForColumn:@"file"] ,
                               [timerRs stringForColumn:@"img"]];
                    
                    if(!isRunOK)
                    {
                        isOK = NO;
                        *rollback = YES;
                        return;
                    }
                }
                [timerRs close];
            }
        }
        [rs close];
   
        isOK = YES;
    }];
    return isOK;
}


-(BOOL) deleMusic:(int) clocktimeId
{
    __block BOOL isOk = NO;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        isOk = [db executeUpdateWithFormat:@"delete from clock_music where clocktime_id=%i",clocktimeId];
    }];
    return isOk;
}


-(BOOL) addTimerOnly:(int) timerid weekday:(int) weekday beginTime:(int) beginTime endTime:(int) endTime
{
    __block BOOL isOk = NO;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        isOk = [db executeUpdateWithFormat:@"insert into clock_timer (timerid ,weekday ,beginTime ,endTime) values (%i ,%i ,%i ,%i)" ,timerid ,weekday ,beginTime ,endTime];
    }];
    return isOk;
}


-(BOOL) getIsOpen:(int) _id
{
    __block BOOL isOk = NO;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        
        FMResultSet *rs;
        rs = [db executeQueryWithFormat:@"select isopen from clock_timer where _id=%i" ,_id];
        while (rs.next) {
            isOk = [rs intForColumn:@"isopen"];
        }
    }];
    return isOk;
}


-(BOOL) setIsOpen:(int) _id flag:(BOOL) flag
{
    __block BOOL isOk = NO;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        isOk = [db executeUpdateWithFormat:@"update clock_timer set isopen = %i where _id=%i" ,flag ,_id];
    }];
    return isOk;
}


// 删除整个定时(同时删除三个表, 先删除音乐表, 再删除时间表, 再删除定时表)
-(BOOL) deleClockTimer:(int) clockid
{
    __block BOOL isOk = NO;
    
    [[YbrSQLHelper getDataBase] inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *rs;
        
        rs = [db executeQueryWithFormat:@"select _id from clock_timer where timerid=%i" ,clockid];
        while (rs.next)
        {
            int _id = [rs intForColumn:@"_id"];
            [db executeUpdateWithFormat:@"delete from clock_timer where _id=%i" ,_id];
            [db executeUpdateWithFormat:@"delete from clock_music where clocktime_id=%i" ,_id];
        }
        [rs close];
        
        isOk = [db executeUpdateWithFormat:@"delete from clock where _id=%i" ,clockid];
    }];
    return isOk;
}


-(BOOL) renameClock:(int) _id rename:(NSString *) rename
{
    __block BOOL isOk = NO;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        isOk = [db executeUpdateWithFormat:@"update clock set name=%@ where _id=%i" ,rename ,_id];
    }];
    return isOk;
}


-(NSString *) getClockNameById:(int) _id
{
    __block NSString *clockName;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select name from clock where _id=%i" ,_id];
        
        while (rs.next)
        {
            clockName = [rs stringForColumn:@"name"];
            break;
        }
        [rs close];
    }];
    return clockName;
}


-(BOOL) clearWeekdayTime:(int) timerId weekdays:(NSArray *) weekdays
{
    __block BOOL isOk = NO;
    
    [[YbrSQLHelper getDataBase] inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *rs = nil;
        
        for (int i = 0; i < [weekdays count]; i ++)
        {
            rs = [db executeQueryWithFormat:@"select _id from clock_timer where timerid=%i and weekday=%i" ,timerId ,[weekdays[i] intValue]];
            
            while (rs.next)
            {
                [db executeUpdateWithFormat:@"delete from clock_music where clocktime_id=%i" ,[rs intForColumn:@"_id"]];
            }
            isOk = [db executeUpdateWithFormat:@"delete from clock_timer where timerid=%i and weekday=%i" ,timerId ,[weekdays[i] intValue]];
        }
        [rs close];
        
    }];
    return isOk;
}


-(NSString *) getClockNameNoRep: (NSString *) name
{
    __block NSString *noRepName;
    __block NSString *myName = name;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        BOOL flag = YES;
        int position = 0;
        FMResultSet *rs = nil;
        
        while (flag)
        {
            rs = [db executeQueryWithFormat:@"select name from clock where name=%@" ,myName];
            
            if(!rs.next)
            {
                flag = NO;
                continue;
            }
            
            position ++;
            myName = [NSString stringWithFormat:@"%@ %i" ,name ,position];
        }
        [rs close];
        noRepName = myName;
    }];
    return noRepName;
}
@end
